ノンインタラクティヴにPostgreSQLの認証情報を渡す
はじめに
バッチ処理などでシェルスクリプトからSQLを実行したい時があります。 そこで少し面倒なのがパスワードの管理です。
日常オペレーションでコマンドラインツールからDBに接続する場合は、インタラクティヴにパスワードを入力すれば済みますが、バッチ用シェルスクリプトでは、ユーザーの手を介さず(=ノンインタラクティヴ)に認証を突破しなければいけません。
今回はPostgreSQL(をベースにしたRedshift)を対象に、認証情報を渡す方法についてまとめてみたいと思います。
ノンインタラクティブに認証情報を渡す
ノンインタラクティブに認証情報を渡す方法として
.pgpassファイル- 環境変数(非推奨)
を順に見ていきましょう。
認証情報を.pgpassファイルで渡す
まずは$HOME/.pgpassに接続・認証情報を記載します。
.pgpassファイルでは各項目を:で区切り、YOUR_HOST:YOUR_PORT:YOUR_DB_NAME:USER:PASSWORDと並べます。
$ vi ~/.pgpass $ chmod 600 ~/.pgpass $ cat ~/.pgpass YOUR_HOST:YOUR_PORT:YOUR_DB_NAME:USER:PASSWORD
疎通確認的なSQLを投げてみましょう。
$ psql -h YOUR_HOST -p YOUR_PORT -U USR -d YOUR_TABLE_NAME -c 'select sysdate'
timestamp
----------------------------
2015-08-11 05:46:11.057386
(1 row)
パスワード入力を求められることなく、SQLを実行できました。
.pgpassファイルのパーミッション
.pgpassファイルはプロセスの実行ユーザだけが参照できるようにパーミッション(600)を設定します。
仮に644(rw-r--r--)のようなゆるいパーミッションの場合、以下のような警告メッセージが表示され、.pgpassファイルは利用されません。
$ psql -h YOUR_HOST WARNING: password file "/home/jsmith/.pgpass" has group or world access; permissions should be u=rw (0600) or less ...
.pgpassファイルのパスをカスタマイズ
.pgpassのパスは環境変数PGPASSFILEでカスタマイズできます。
RedshiftのCOPYコマンドで実験してみましょう。
そのものズバリのBashスクリプト(redshift.sh )がAWS サイトの Articles & Tutorials として公開されています。
こちらの中身を確認してみましょう。
#!/bin/bash
DBHOST=YOUR_HOST
DBPORT=YOUR_PORT
DBNAME=YOUR_DB
DBTABLE=YOUR_TABLE_NAME
DBUSER=YOUR_DB_USER
DBPASS=YOUR_PASSWORD
S3PATH=s3://YOUR_CF_LOGS_PREFIX # {bucket-name}/{optional-prefix}{distribution-ID}
YESTERDAY=`date -d "yesterday" +"%Y-%m-%d"`
CF_LOG_PREFIX=$S3PATH.$YESTERDAY
AWS_ACCESS_KEY_ID=XXXXXXX
AWS_SECRET_ACCESS_KEY=XXXXXXXXXXXXXXX
# Secure temp files
export PGPASSFILE=`mktemp /tmp/pass.XXXXXX`
cmds=`mktemp /tmp/cmds.XXXXXX`
logs=`mktemp /tmp/logs.XXXXXX`
cat >$PGPASSFILE << EOF
$DBHOST:$DBPORT:$DBNAME:$DBUSER:$DBPASS
EOF
cat > $cmds << EOF
copy $DBTABLE from '$CF_LOG_PREFIX' CREDENTIALS 'aws_access_key_id=$AWS_ACCESS_KEY_ID;aws_secret_access_key=$AWS_SECRET_ACCESS_KEY' delimiter '\t' MAXERROR 200 IGNOREHEADER 2 gzip;
EOF
psql -d $DBNAME -h $DBHOST -p $DBPORT -U $DBUSER -f $cmds >$logs 2>&1
このスクリプトでは
PGPASSFILE(パスワードファイルのパス)- cmds(
COPYコマンド文) - logs(実行ログ)
の3ファイルをmktempコマンドでランダムなファイル名で生成し、最後の行のpsqlコマンドでSQLを投げています。
実際にこのシェルスクリプトを実行すると、logsファイルには
psql:/tmp/cmds.4iIrc3:1: INFO: Load into table 'customer' completed, 3000000 record(s) loaded successfully. COPY
というようにログ出力されます。
認証情報を環境変数で渡す(非推奨)
PostgreSQLでは接続時の情報を環境変数で渡せます。
具体的には
- PGHOST
- PGPORT
- PGDATABASE
- PGUSER
- PGPASSWORD
などで、各変数が定義している内容は変数名から自明かと思います。
この環境変数を使ってRedshiftにアクセスしてみましょう。
$ PGHOST=YOUR_HOST PGPORT=YOUR_PORT PGDATABASE=YOUR_TABLE_NAME PGUSER=USER PGPASSWORD=PASSWORD psql -c 'select sysdate'
timestamp
----------------------------
2015-08-11 06:05:00.411914
(1 row)
パスワード入力を求められることなく、SQLを実行できました。
この方式はお手軽ではあるのですが、プロセスの環境変数は覗き見しやすいため、セキュリティの観点からマニュアルでは"Use of this environment variable is not recommended for security reasons"というように非推奨となっています。
MySQLにもMYSQL_PWDという同様の環境変数が定義されています。
マニュアルでの扱いを確認したところ "This method of specifying your MySQL password must be considered extremely insecure and should not be used." というようにもっと強い口調で否定されていました。
特別な理由がない限り、環境変数でパスワードを渡すことは避けましょう。
